package com.jynn.mesh.controller;

import com.jynn.mesh.entity.Student;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

@RestController
public class ImportExportExcelController {
	
	@RequestMapping(value="/importExcel")
	public void ImportRoutineFile(@RequestParam(required = false,value="excelPath") String excelPath){
		excelPath = "/Users/liuwenxu/Documents/student.xlsx";
		try { 
			File excel = new File(excelPath); 
			if (excel.isFile() && excel.exists()) { 
				FileInputStream inputStream = new FileInputStream(excel);
				@SuppressWarnings("resource")
				Workbook wb = new XSSFWorkbook(inputStream); 
				//开始解析
				Sheet sheet = wb.getSheetAt(0);     //读取sheet 0 
				int firstRowIndex = sheet.getFirstRowNum()+1;   //第一行是列名，所以不读 
				int lastRowIndex = sheet.getLastRowNum();  
				for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   //遍历行 
					System.out.println("rIndex: " + rIndex); 
					Row row = sheet.getRow(rIndex); 
					Student student = null;
					if (row != null) {
						if(row.getCell(0)!=null && row.getCell(0).toString()!=""){
							System.out.println(row.getCell(0).toString());
							student = new Student();
							if(row.getCell(0)!=null && row.getCell(0).toString()!=""){
								student.setId(row.getCell(0).toString());
							}
							if(row.getCell(1)!=null && row.getCell(1).toString()!=""){
								student.setName(row.getCell(1).toString());
							}
							if(row.getCell(2)!=null && row.getCell(2).toString()!=""){
								student.setClasses(row.getCell(2).toString());
							}
							if(row.getCell(3)!=null && row.getCell(3).toString()!=""){
								student.setScore(String.valueOf(row.getCell(3).toString()));
							}
							System.out.println(student);
						}
					}
				} 
			} else { 
				System.out.println("找不到指定的文件");
			} 
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}
	
	@RequestMapping(value="/exportExcel")
	public void ExportRoutineFile(HttpServletResponse response) throws IOException {
		List<Student> studentList = new ArrayList<>();
		Student student1 = new Student();
		student1.setId("101");
		student1.setName("孙悟空");
		student1.setClasses("龟仙门");
		student1.setScore("100");
		studentList.add(student1);
		
		Student student2 = new Student();
		student2.setId("102");
		student2.setName("库林");
		student2.setClasses("龟仙门");
		student2.setScore("90");
		studentList.add(student2);
		
		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		//设置样式
		HSSFCellStyle style = wb.createCellStyle(); // 样式对象  
		
		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("学生信息表");
        
       	HSSFCell cell = null;
       	// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
       	CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,4);//起始行,结束行,起始列,结束列  
        HSSFRow row = sheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellValue("学生信息表");
        cell.setCellStyle(style);
        sheet.addMergedRegion(callRangeAddress);
        //HSSFDataValidation validate = PersonnelRoutineFeignService.setBoxs();
        //sheet.addValidationData(validate);
        //excel标题
        String[] title = {"编号","名称","班级","分数"};
        HSSFRow row1 = sheet.createRow(1);
        for(int j = 0; j < title.length; j++){
       	 cell = row1.createCell(j);
       	 cell.setCellValue(title[j]);
        }
        
    	if(studentList != null){
	        for (int i = 0; i < studentList.size(); i++) {
	        	//循环插入数据
	        	if(studentList.get(i)!=null){
		            HSSFRow row2 = sheet.createRow(i+2); 
//		            cell = row2.createCell(0);
//		            cell.setCellValue(i+1);
		            cell = row2.createCell(1);
		            cell.setCellValue(studentList.get(i).getId());
		            cell = row2.createCell(2);
		            cell.setCellValue(studentList.get(i).getName());
		            cell = row2.createCell(3);
		            cell.setCellValue(studentList.get(i).getClasses());
		            cell = row2.createCell(4);
		            cell.setCellValue(studentList.get(i).getScore());
		        }
	        }
        }
        try {
//			File t = new File("/Users/liuwenxu/Documents/learn/myport-master/src/main/resources/resources/tmp.xlsx");
			
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment;filename=basicinfo.xls");// 默认Excel名称
			response.flushBuffer();
			wb.write(response.getOutputStream());
			OutputStream out = response.getOutputStream();
			
//			Base64 encoder = new Base64();

//			InputStream in = new FileInputStream(t);

//			byte[] b = new byte[3*1000];
//			byte[] str = null;
//			int count;
//			while (( count = in.read(b)) != -1) {
//				if(count!=b.length) {
//					byte[] copy = Arrays.copyOf(b, count);
//					str= encoder.encodeBase64(copy);
//					} else {
//					str = encoder.encodeBase64(b);
//				}	
//				out.write(str,0,str.length);
//				out.flush();
//			}
			
//			in.close();
			out.close();
//			t.delete();
        } catch (Exception e1) {
            e1.printStackTrace();
        }
	}
}
